/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import model.dao.util.MySQLDataAccessHelper;
import model.pojo.baivietPOJO;
import model.pojo.mathangPOJO;

/**
 *
 * @author lethong
 */
public class mathangDAO {

    public static ArrayList<mathangPOJO> getlistMathang_danhmuc(String loai) throws Exception {
        ArrayList<mathangPOJO> ds = new ArrayList<mathangPOJO>();
        try {
            String sql = "Select * From mathangkhuyenmai where Madanhmuc='" + loai + "'";
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                mathangPOJO s = new mathangPOJO();
                s.setId(rs.getInt("id"));
                s.setTenmathang(rs.getString("Tenmathang"));
                s.setMadanhmuc(rs.getString("Madanhmuc"));
                s.setGiathuc(rs.getFloat("Giathuc"));
                s.setGiakhuyenmai(rs.getFloat("Giakhuyenmai"));
                s.setHinhanh(rs.getString("Hinhanh"));
                s.setTinhtrang(rs.getInt("Tinhtrang"));
                s.setNgaybatdau(rs.getDate("Ngaybatdau"));
                s.setNgaybatdau(rs.getDate("Ngayketthuc"));
                s.setSoluongkhuyenmai(rs.getInt("Soluongkhuyenmai"));
                s.setSoluongdatmua(rs.getInt("Soluongdatmua"));
                s.setNoidung(rs.getString("Noidung"));
                s.setDiemnoibat(rs.getString("Diemnoibat"));
                s.setDieukien(rs.getString("Dieukien"));
                s.setThongtinchitiet(rs.getString("Thongtinchitiet"));
                ds.add(s);
            }
            helper.close();
        } catch (Exception ex) {
            throw ex;
        }
        return ds;
    }

    public static ArrayList<mathangPOJO> getlistMathang_giamgiahomnay(String ngaybd) throws Exception {
        ArrayList<mathangPOJO> ds = new ArrayList<mathangPOJO>();
        try {
            SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
            String date = ngaybd;
            java.util.Date utilDate = formatter.parse(date);
            java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
            String sql = "Select * From mathangkhuyenmai where Ngaybatdau='" + sqlDate + "' and Tinhtrang=1";
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                mathangPOJO s = new mathangPOJO();
                s.setId(rs.getInt("id"));
                s.setTenmathang(rs.getString("Tenmathang"));
                s.setMadanhmuc(rs.getString("Madanhmuc"));
                s.setGiathuc(rs.getFloat("Giathuc"));
                s.setGiakhuyenmai(rs.getFloat("Giakhuyenmai"));
                s.setHinhanh(rs.getString("Hinhanh"));
                s.setTinhtrang(rs.getInt("Tinhtrang"));
                s.setNgaybatdau(rs.getDate("Ngaybatdau"));
                s.setNgayketthuc(rs.getDate("Ngayketthuc"));
                s.setSoluongkhuyenmai(rs.getInt("Soluongkhuyenmai"));
                s.setSoluongdatmua(rs.getInt("Soluongdatmua"));
                s.setNoidung(rs.getString("Noidung"));
                s.setDiemnoibat(rs.getString("Diemnoibat"));
                s.setDieukien(rs.getString("Dieukien"));
                s.setThongtinchitiet(rs.getString("Thongtinchitiet"));
                ds.add(s);
            }
            helper.close();
        } catch (Exception ex) {
            throw ex;
        }
        return ds;
    }

    public static ArrayList<mathangPOJO> getlistMathang_giatotganday(String ngaybd) throws Exception {
        ArrayList<mathangPOJO> ds = new ArrayList<mathangPOJO>();
        try {
            SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
            String date = ngaybd;
            java.util.Date utilDate = formatter.parse(date);
            java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
            String sql = "Select * From mathangkhuyenmai where Ngaybatdau<'" + sqlDate + "' and tinhtrang=1";
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                mathangPOJO s = new mathangPOJO();
                s.setId(rs.getInt("id"));
                s.setTenmathang(rs.getString("Tenmathang"));
                s.setMadanhmuc(rs.getString("Madanhmuc"));
                s.setGiathuc(rs.getFloat("Giathuc"));
                s.setGiakhuyenmai(rs.getFloat("Giakhuyenmai"));
                s.setHinhanh(rs.getString("Hinhanh"));
                s.setTinhtrang(rs.getInt("Tinhtrang"));
                s.setNgaybatdau(rs.getDate("Ngaybatdau"));
                s.setNgayketthuc(rs.getDate("Ngayketthuc"));
                s.setSoluongkhuyenmai(rs.getInt("Soluongkhuyenmai"));
                s.setSoluongdatmua(rs.getInt("Soluongdatmua"));
                s.setNoidung(rs.getString("Noidung"));
                s.setDiemnoibat(rs.getString("Diemnoibat"));
                s.setDieukien(rs.getString("Dieukien"));
                s.setThongtinchitiet(rs.getString("Thongtinchitiet"));
                ds.add(s);
            }
            helper.close();
        } catch (Exception ex) {
            throw ex;
        }
        return ds;
    }

    public static ArrayList<mathangPOJO> getlistMathang_giatotdaban(String ngaykt) throws Exception {
        ArrayList<mathangPOJO> ds = new ArrayList<mathangPOJO>();
        try {
            SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
            String date = ngaykt;
            java.util.Date utilDate = formatter.parse(date);
            java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
            String sql = "Select * From mathangkhuyenmai where Ngayketthuc='" + sqlDate + "' or tinhtrang=0";
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                mathangPOJO s = new mathangPOJO();
                s.setId(rs.getInt("id"));
                s.setTenmathang(rs.getString("Tenmathang"));
                s.setMadanhmuc(rs.getString("Madanhmuc"));
                s.setGiathuc(rs.getFloat("Giathuc"));
                s.setGiakhuyenmai(rs.getFloat("Giakhuyenmai"));
                s.setHinhanh(rs.getString("Hinhanh"));
                s.setTinhtrang(rs.getInt("Tinhtrang"));
                s.setNgaybatdau(rs.getDate("Ngaybatdau"));
                s.setNgayketthuc(rs.getDate("Ngayketthuc"));
                s.setSoluongkhuyenmai(rs.getInt("Soluongkhuyenmai"));
                s.setSoluongdatmua(rs.getInt("Soluongdatmua"));
                s.setNoidung(rs.getString("Noidung"));
                s.setDiemnoibat(rs.getString("Diemnoibat"));
                s.setDieukien(rs.getString("Dieukien"));
                s.setThongtinchitiet(rs.getString("Thongtinchitiet"));
                ds.add(s);
            }
            helper.close();
        } catch (Exception ex) {
            throw ex;
        }
        return ds;
    }

    public static ArrayList<mathangPOJO> getinfo_Mathang(int ma) throws Exception {
        ArrayList<mathangPOJO> ds = new ArrayList<mathangPOJO>();
        try {
            String sql = "Select * From mathangkhuyenmai where id=" + ma;
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                mathangPOJO s = new mathangPOJO();
                s.setId(rs.getInt("id"));
                s.setTenmathang(rs.getString("Tenmathang"));
                s.setMadanhmuc(rs.getString("Madanhmuc"));
                s.setGiathuc(rs.getFloat("Giathuc"));
                s.setGiakhuyenmai(rs.getFloat("Giakhuyenmai"));
                s.setHinhanh(rs.getString("Hinhanh"));
                s.setTinhtrang(rs.getInt("Tinhtrang"));
                s.setNgaybatdau(rs.getDate("Ngaybatdau"));
                s.setNgayketthuc(rs.getDate("Ngayketthuc"));
                s.setSoluongkhuyenmai(rs.getInt("Soluongkhuyenmai"));
                s.setSoluongdatmua(rs.getInt("Soluongdatmua"));
                s.setNoidung(rs.getString("Noidung"));
                s.setDiemnoibat(rs.getString("Diemnoibat"));
                s.setDieukien(rs.getString("Dieukien"));
                s.setThongtinchitiet(rs.getString("Thongtinchitiet"));
                ds.add(s);
            }
            helper.close();
        } catch (Exception ex) {
            throw ex;
        }
        return ds;
    }

    public static ArrayList<mathangPOJO> getlistMathang_giatotganday_cophantrang(String ngaybd, int trang) throws ParseException {
        ArrayList<mathangPOJO> ds = new ArrayList<mathangPOJO>();
        int soSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(2);
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
        String date = ngaybd;
        java.util.Date utilDate = formatter.parse(date);
        java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
        String sql = String.format("Select * From mathangkhuyenmai where Ngaybatdau<'%s' and Ngayketthuc>'%s' and tinhtrang=1 LIMIT %d,%d",
                sqlDate, sqlDate, batDau, soSanPhamTrenTrang);
        try {
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                mathangPOJO s = new mathangPOJO();
                s.setId(rs.getInt("id"));
                s.setTenmathang(rs.getString("Tenmathang"));
                s.setMadanhmuc(rs.getString("Madanhmuc"));
                s.setGiathuc(rs.getFloat("Giathuc"));
                s.setGiakhuyenmai(rs.getFloat("Giakhuyenmai"));
                s.setHinhanh(rs.getString("Hinhanh"));
                s.setTinhtrang(rs.getInt("Tinhtrang"));
                s.setNgaybatdau(rs.getDate("Ngaybatdau"));
                s.setNgayketthuc(rs.getDate("Ngayketthuc"));
                s.setSoluongkhuyenmai(rs.getInt("Soluongkhuyenmai"));
                s.setSoluongdatmua(rs.getInt("Soluongdatmua"));
                s.setNoidung(rs.getString("Noidung"));
                s.setDiemnoibat(rs.getString("Diemnoibat"));
                s.setDieukien(rs.getString("Dieukien"));
                s.setThongtinchitiet(rs.getString("Thongtinchitiet"));
                ds.add(s);
            }
            helper.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }

    //<editor-fold defaultstate="collapsed" desc="lấy số lượng sản phẩm trang giá tốt gần đây">
    public static int laySoLuongSanPham(String ngaybd) throws ParseException {
        SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
        String date = ngaybd;
        java.util.Date utilDate = formatter.parse(date);
        java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
        String sql = String.format("SELECT COUNT(*) AS SOLUONG FROM mathangkhuyenmai WHERE Ngaybatdau<'%s' and Ngayketthuc>'%s' and tinhtrang=1", sqlDate, sqlDate);
        int soLuong = -1;
        try {
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                soLuong = rs.getInt("SoLuong");
            }
            helper.close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return soLuong;
    }

    public static int tinhSoLuongTrang(String ngaybd) throws ParseException {
        int soTrang;
        int soLuongSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(2);
        int soLuong = laySoLuongSanPham(ngaybd);
        soTrang = soLuong / soLuongSanPhamTrenTrang;
        if (soLuong % soLuongSanPhamTrenTrang != 0) {
            soTrang++;
        }
        return soTrang;
    }

    public static int soSanPhamTheoTrang(String ngaybd, int trang) throws ParseException {
        int soLuongSanPhamToiDaTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(2);
        int tongSoLuong = laySoLuongSanPham(ngaybd);
        int soTrang = tongSoLuong / soLuongSanPhamToiDaTrenTrang;
        if (tongSoLuong % soLuongSanPhamToiDaTrenTrang != 0) {
            soTrang++;
        }
        int soLuong;
        if (trang < soTrang) {
            soLuong = soLuongSanPhamToiDaTrenTrang;
        } else {
            soLuong = tongSoLuong % soLuongSanPhamToiDaTrenTrang;
        }
        return soLuong;

    }
    //</editor-fold>
    
    public static ArrayList<mathangPOJO> getlistMathang_giatotdaban_cophantrang(String ngaykt, int trang) throws ParseException {
        ArrayList<mathangPOJO> ds = new ArrayList<mathangPOJO>();
        int soSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(2);
        int batDau = (trang - 1) * soSanPhamTrenTrang;
        SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
        String date = ngaykt;
        java.util.Date utilDate = formatter.parse(date);
        java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
        String sql = String.format("Select * From mathangkhuyenmai where Ngayketthuc<='%s' or tinhtrang=0 LIMIT %d,%d",
                sqlDate, batDau, soSanPhamTrenTrang);
        try {
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            while (rs.next()) {
                mathangPOJO s = new mathangPOJO();
                s.setId(rs.getInt("id"));
                s.setTenmathang(rs.getString("Tenmathang"));
                s.setMadanhmuc(rs.getString("Madanhmuc"));
                s.setGiathuc(rs.getFloat("Giathuc"));
                s.setGiakhuyenmai(rs.getFloat("Giakhuyenmai"));
                s.setHinhanh(rs.getString("Hinhanh"));
                s.setTinhtrang(rs.getInt("Tinhtrang"));
                s.setNgaybatdau(rs.getDate("Ngaybatdau"));
                s.setNgayketthuc(rs.getDate("Ngayketthuc"));
                s.setSoluongkhuyenmai(rs.getInt("Soluongkhuyenmai"));
                s.setSoluongdatmua(rs.getInt("Soluongdatmua"));
                s.setNoidung(rs.getString("Noidung"));
                s.setDiemnoibat(rs.getString("Diemnoibat"));
                s.setDieukien(rs.getString("Dieukien"));
                s.setThongtinchitiet(rs.getString("Thongtinchitiet"));
                ds.add(s);
            }
            helper.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        return ds;
    }
    
    //<editor-fold defaultstate="collapsed" desc="Lấy số lượng sản phẩm trang giá tốt đã bán">
    public static int laySoLuongSanPham_daban(String ngay) throws ParseException {
        SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
        String date = ngay;
        java.util.Date utilDate = formatter.parse(date);
        java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
        String sql = String.format("SELECT COUNT(*) AS SOLUONG FROM mathangkhuyenmai WHERE Ngayketthuc<='%s' or tinhtrang=0", sqlDate);
        int soLuong = -1;
        try {
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.open();
            ResultSet rs = helper.executeQuery(sql);
            if (rs.next()) {
                soLuong = rs.getInt("SoLuong");
            }
            helper.close();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return soLuong;
    }
    
    public static int tinhSoLuongTrang_daban(String ngay) throws ParseException {
        int soTrang;
        int soLuongSanPhamTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(2);
        int soLuong = laySoLuongSanPham_daban(ngay);
        soTrang = soLuong / soLuongSanPhamTrenTrang;
        if (soLuong % soLuongSanPhamTrenTrang != 0) {
            soTrang++;
        }
        return soTrang;
    }
    
    public static int soSanPhamTheoTrang_daban(String ngay, int trang) throws ParseException {
        int soLuongSanPhamToiDaTrenTrang = ThamSoDAO.laySoLuongSanPhamTrenTrang(2);
        int tongSoLuong = laySoLuongSanPham_daban(ngay);
        int soTrang = tongSoLuong / soLuongSanPhamToiDaTrenTrang;
        if (tongSoLuong % soLuongSanPhamToiDaTrenTrang != 0) {
            soTrang++;
        }
        int soLuong;
        if (trang < soTrang) {
            soLuong = soLuongSanPhamToiDaTrenTrang;
        } else {
            soLuong = tongSoLuong % soLuongSanPhamToiDaTrenTrang;
        }
        return soLuong;
        
    }
    //</editor-fold>
}
